package com.example.demo_text;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class CourseDAO {

    private static final Logger logger = LoggerFactory.getLogger(CourseDAO.class);

    /**
     * 检查课程名称是否已存在
     */
    public static boolean isCourseNameExists(String courseName) {
        String sql = "SELECT COUNT(*) FROM course WHERE course_name = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseName);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (Exception e) {
            logger.error("检查课程名称是否存在失败", e);
        }
        return false;
    }

    /**
     * 添加新课程并返回生成的ID
     */
    public static int addCourseAndGetId(String courseName) {
        String sql = "INSERT INTO course(course_name) VALUES (?)";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, courseName);
            int affectedRows = ps.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("创建课程失败，未获得插入ID");
            }

            try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getInt(1); // 返回新增记录的 course_id
                } else {
                    throw new SQLException("获取插入ID失败");
                }
            }

        } catch (Exception e) {
            logger.error("添加课程并获取ID失败", e);
            return -1; // 插入失败
        }
    }
/**
 * 根据课程ID获取课程名称
 */
public static String getCourseNameById(String courseIdStr) {
    if (courseIdStr == null || courseIdStr.trim().isEmpty()) {
        return "";
    }

    int courseId;
    try {
        courseId = Integer.parseInt(courseIdStr);
    } catch (NumberFormatException e) {
        // 参数不是有效的整数
        return "";
    }

    String sql = "SELECT course_name FROM course WHERE course_id = ?";
    try (Connection conn = UserDAO.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {

        stmt.setInt(1, courseId);
        try (ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getString("course_name");
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    return ""; // 没有找到返回空字符串
}
    // CourseDAO.java 新增方法
    public static List<Course> getCoursesByUserId(int userId) {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT c.course_id, c.course_name " +
                "FROM course c " +
                "JOIN student_course sc ON c.course_id = sc.course_id " +
                "WHERE sc.user_id = ?";

        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Course course = new Course();
                    course.setId(rs.getInt("course_id"));
                    course.setName(rs.getString("course_name"));
                    courses.add(course);
                }
            }
        } catch (Exception e) {
            logger.error("根据用户ID获取课程失败", e);
        }
        return courses;
    }

    /**
     * 获取所有课程
     */
    public static List<Course> getAllCourses() {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT * FROM course";

        try (Connection conn = UserDAO.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Course course = new Course();
                course.setId(rs.getInt("course_id"));
                course.setName(rs.getString("course_name"));
                courses.add(course);
            }

        } catch (Exception e) {
            logger.error("获取课程列表失败", e);
        }
        return courses;
    }

    /**
     * 根据课程ID获取课程信息
     */
    public static Course getCourseById(int courseId) {
        String sql = "SELECT * FROM course WHERE course_id = ?";

        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Course course = new Course();
                    course.setId(rs.getInt("course_id"));
                    course.setName(rs.getString("course_name"));
                    return course;
                }
            }

        } catch (Exception e) {
            logger.error("根据课程ID查询失败", e);
        }
        return null;
    }

    /**
     * 添加新课程（仅添加名称）
     */
    public static boolean addCourse(Course course) {
        String sql = "INSERT INTO course(course_name) VALUES (?)";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getName());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            logger.error("添加课程失败", e);
            return false;
        }
    }

    /**
     * 更新课程信息
     */
    public static boolean updateCourse(Course course) {
        String sql = "UPDATE course SET course_name = ? WHERE course_id = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getName());
            ps.setInt(2, course.getId());
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            logger.error("更新课程失败", e);
            return false;
        }
    }

    /**
     * 删除课程
     */
    public static boolean deleteCourse(int courseId) {
        String sql = "DELETE FROM course WHERE course_id = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } catch (Exception e) {
            logger.error("删除课程失败", e);
            return false;
        }
    }
}
